package com.ibm.spatiotemp.server.framework.processor.ifx;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import org.apache.log4j.Logger;

import com.fasterxml.jackson.databind.JsonNode;
import com.ibm.json.java.JSONArray;
import com.ibm.json.java.JSONObject;
import com.ibm.spatiotemp.server.framework.request.Request;
import com.ibm.spatiotemp.tools.Constants;
import com.ibm.spatiotemp.tools.CoreMessages;
import com.ibm.spatiotemp.tools.STException;
import com.ibm.spatiotemp.tools.STSPoint;
import com.ibm.spatiotemp.tools.STSPointObject;
import com.ibm.spatiotemp.utils.ConfigManager;

public class IfxFirstTimeService {
	
	public final static Logger logger = Logger.getLogger(IfxFirstTimeService.class);
	
	public JSONArray getFirstTime(Connection conn, Request request) throws SQLException, STException {
		String name = null;
		String value = null;
//		JsonNode bodyNode = request.getRequestbody();
		JsonNode idNode = request.get("id");
		if (idNode != null) {
			name = "id";
			value = idNode.asText();
		} else if (request.get("key") != null) {
			name = "key";
			value = request.get("key").asText();
		}
		if (name == null) {
			throw new STException(CoreMessages.STHS0015E);
		} else {
			return queryFirstTime(conn, request, name, value);
		}
	}
	
	protected JSONArray queryFirstTime(Connection conn, Request request, String name, String value) throws SQLException, STException {
		Statement st = null;
		String id = null;
		String key = null;
		String sqlString = null;
		ResultSet resultSet = null;
		JSONArray jsonArray = null;
		JSONObject json = null;
		STSPointObject pointObject = null;	
		double x;
		double y;
		String posString;
		double distance;
		Timestamp firsttime;
		long time1 = 0;
		long time2, usedtime;
		
		JsonNode bodyNode = request.getJnode();		
		st = conn.createStatement();
		
		// find the id and key		
		if (name.equalsIgnoreCase("id")) {	
			id = value;
			key = null;
		} else {
			sqlString = "select " + Constants.IDCOLUMN_NAME + ", " + Constants.KEYCOLUMN_NAME + " from " + Constants.TABLE_NAME + " where " + Constants.KEYCOLUMN_NAME + " = \'" + value + "\'";
			resultSet = st.executeQuery(sqlString);	
			if (resultSet.next()) {
				id = resultSet.getString(1).trim();
				key = resultSet.getString(2);
				if (key != null) {
					key = key.trim();
				}
			} else {
				throw new STException(String.format(CoreMessages.STHS0013E, name, value));
			}
			resultSet.close();	
		}
		
		String temp = bodyNode.get("start").asText();
		temp = temp.replaceFirst("T", " ");
		String start = "\'" + temp + "\'";
		temp = bodyNode.get("end").asText();
		temp = temp.replaceFirst("T", " ");
		String end = "\'" + temp + "\'";
				
		JsonNode posNode = bodyNode.get("position");
		if (posNode.isArray()) {
			if (posNode.size() == 2) {
				x = posNode.get(0).asDouble();
				y = posNode.get(1).asDouble();
			} else {
				throw new STException(String.format(CoreMessages.STHS0001E, "position", posNode.toString()));
			}
		} else {
			throw new STException(String.format(CoreMessages.STHS0001E, "position", posNode.toString()));
		}
		
		posString = "\'" + Constants.SRID + " point(" + x + " " + y + ")\'";		
		JsonNode disNode = bodyNode.get("distance");
		distance = disNode.asDouble();
		
		try {
			String format = "select %s(\'%s\', \'%s\', %s, %s, %s, %s, %f) from %s where %s=\'%s\'";
			sqlString = String.format(format, Constants.GET_FIRSTTIME, Constants.TABLE_NAME, id, Constants.TSCOLUMN_NAME, start, end, posString, distance, Constants.TABLE_NAME, Constants.IDCOLUMN_NAME, id);
//			logger.info("sql by getftimebypoint =" + sqlString);
			if (ConfigManager.DEBUG_MODE) {
				time1 = System.currentTimeMillis();
			}
			resultSet = st.executeQuery(sqlString);
			if (resultSet.next()) {
				firsttime = resultSet.getTimestamp(1);
				if (ConfigManager.DEBUG_MODE) {
					time2 = System.currentTimeMillis();
					usedtime = time2 - time1;
					logger.info("usedtime=" + usedtime + " for sqlString=" + sqlString);
				}
				if (firsttime != null) {
					// need to get the right position
					resultSet.close();
					sqlString = "select " + Constants.GET_POSITION + "(" + Constants.TSCOLUMN_NAME + ", '" + firsttime.toString() + "') from "
							+ Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id +"\'";
//					logger.info("sql by get right position for time=" + sqlString);
					resultSet = st.executeQuery(sqlString);
					if (resultSet.next()) {
						String pos = resultSet.getString(1);
						if (pos != null) {
							STSPoint point = new STSPoint(firsttime.getTime(), pos);
							pointObject = new STSPointObject();
							pointObject.setSTSPoint(point);
						}
					}					
				}
			}
			resultSet.close();
			if (pointObject != null) {
				jsonArray = new JSONArray();
				json = new JSONObject();
				json.put("id", id);
				if (key != null) {
					json.put("key", key);
				}
				json.put("trajectory", pointObject.toJSONObject());
				jsonArray.add(json);
			}
			st.close();
			return jsonArray;
		} catch (SQLException e1) {
			throw e1;
		} finally {
			try {
				st.close();
			} catch (SQLException e1) {}
		}
	}
}
	
